﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CRMFramework;
using System.Collections;
using Newtonsoft.Json;
using CRMEntity;

namespace CRMBiz
{
    public static class LedgerHandler
    {
        /* this statement is use to show data in gridview */
        public static DataTable GetLedgerPrint(int LoanID)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sbSql = "SELECT CAST(DATE_FORMAT(date, '%d/%m/%Y' )AS CHAR) AS date,description,amount,balance FROM am_ledger1 a where loanid=@LoanID";
                dt = DBhelper.ExecuteDataset(sbSql, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        public static DataSet GetLedgerList(int pageIndex, int pageSize, int LoanID)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                var sbSql = new StringBuilder("SELECT ledgerid,date,description,amount,debit,credit,balance,(select count(*) from am_ledger1) as Total FROM am_ledger1 a where loanid=@LoanID");
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static int AddLedger(string LedgerDate, string TransactionsCharge, string Repayment1, string DebitAmount, string CreditAmount, string ClosingBalance, string LoanID)
        {
            int intReturnValue = 0;
            try
            {
                if (LedgerDate == "")
                    LedgerDate = null;

                var para = new[] {
                DBhelper.SetParameter("@LedgerDate", LedgerDate),
                DBhelper.SetParameter("@TransactionsCharge", TransactionsCharge),
                DBhelper.SetParameter("@Repayment1", Repayment1),
                DBhelper.SetParameter("@DebitAmount", DebitAmount),
                DBhelper.SetParameter("@CreditAmount", CreditAmount),
                DBhelper.SetParameter("@ClosingBalance", ClosingBalance),
                DBhelper.SetParameter("@LoanID", LoanID),
               
                };
                string sqlInertRepaymentQuety = "insert into am_ledger1(date, amount, debit, credit, balance, LoanID, description) values(STR_TO_DATE(@LedgerDate,'%m/%d/%Y'),@Repayment1,@DebitAmount,@CreditAmount,@ClosingBalance,@LoanID,@TransactionsCharge);SELECT ledgerid FROM `am_ledger1` order by  ledgerid desc limit 1";
                DBhelper.ExecuteDataset(sqlInertRepaymentQuety, para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int EditLedger(int ledgerid, string LedgerDate, string TransactionsCharge, string Repayment1, string DebitAmount, string CreditAmount, string ClosingBalance)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ledgerid", ledgerid),
                    DBhelper.SetParameter("@LedgerDate", LedgerDate),
                    DBhelper.SetParameter("@TransactionsCharge", TransactionsCharge),
                    DBhelper.SetParameter("@Repayment1", Repayment1),
                    DBhelper.SetParameter("@DebitAmount", DebitAmount),
                    DBhelper.SetParameter("@CreditAmount", CreditAmount),
                    DBhelper.SetParameter("@ClosingBalance", ClosingBalance)
                };

                string sbSql = "update am_ledger1 set date = STR_TO_DATE(@LedgerDate,'%m/%d/%Y'),description=@TransactionsCharge,amount=@Repayment1,debit=@DebitAmount,credit=@CreditAmount,balance=@ClosingBalance where ledgerid = @ledgerid";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static bool DeleteLedger(int ledgerid)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@LedgerId", ledgerid),
                };

                string sbSql = "delete from am_ledger1 where ledgerid=@LedgerId";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static DataTable getClosingBalance(int LoanID)
        {
            DataTable dt = null;
            try
            {
                var para = new[] {
                DBhelper.SetParameter("@LoanID", LoanID)
                };
                string sqlqry = "SELECT balance FROM am_ledger1 where loanid=@LoanID order by  ledgerid desc limit 1";
                dt = DBhelper.ExecuteDataset(sqlqry, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }
    }
}
